MySQL Community Server 5.7.18 deb安装以及实现主从复制

1.从oracle官网下载mysql安装包。

mysql-server_5.7.18-1ubuntu16.04_amd64.deb-bundle.tar

2.解压缩安装包。

tar -xf mysql-server_5.7.18-1ubuntu16.04_amd64.deb-bundle.tar

3.使用root用户执行命令安装。

dpkg -i mysql-common_5.7.18-1ubuntu16.04_amd64.deb mysql-community-client_5.7.18-1ubuntu16.04_amd64.deb libmysqlclient20_5.7.18-1ubuntu16.04_amd64.deb libmysqlclient-dev_5.7.18-1ubuntu16.04_amd64.deb libmysqld-dev_5.7.18-1ubuntu16.04_amd64.deb mysql-client_5.7.18-1ubuntu16.04_amd64.deb mysql-community-server_5.7.18-1ubuntu16.04_amd64.deb

4.报错如下。

root@ubuntu:~$ dpkg -i mysql-common_5.7.18-1ubuntu16.04_amd64.deb mysql-community-client_5.7.18-1ubuntu16.04_amd64.deb libmysqlclient20_5.7.18-1ubuntu16.04_amd64.deb libmysqlclient-dev_5.7.18-1ubuntu16.04_amd64.deb libmysqld-dev_5.7.18-1ubuntu16.04_amd64.deb mysql-client_5.7.18-1ubuntu16.04_amd64.deb mysql-community-server_5.7.18-1ubuntu16.04_amd64.deb
Selecting previously unselected package mysql-common.
(Reading database ... 207638 files and directories currently installed.)
Preparing to unpack mysql-common_5.7.18-1ubuntu16.04_amd64.deb ...
Unpacking mysql-common (5.7.18-1ubuntu16.04) ...
Selecting previously unselected package mysql-community-client.
Preparing to unpack mysql-community-client_5.7.18-1ubuntu16.04_amd64.deb ...
Unpacking mysql-community-client (5.7.18-1ubuntu16.04) ...
Selecting previously unselected package libmysqlclient20:amd64.
Preparing to unpack libmysqlclient20_5.7.18-1ubuntu16.04_amd64.deb ...
Unpacking libmysqlclient20:amd64 (5.7.18-1ubuntu16.04) ...
Selecting previously unselected package libmysqlclient-dev.
Preparing to unpack libmysqlclient-dev_5.7.18-1ubuntu16.04_amd64.deb ...
Unpacking libmysqlclient-dev (5.7.18-1ubuntu16.04) ...
Selecting previously unselected package libmysqld-dev.
Preparing to unpack libmysqld-dev_5.7.18-1ubuntu16.04_amd64.deb ...
Unpacking libmysqld-dev (5.7.18-1ubuntu16.04) ...
Selecting previously unselected package mysql-client.
Preparing to unpack mysql-client_5.7.18-1ubuntu16.04_amd64.deb ...
Unpacking mysql-client (5.7.18-1ubuntu16.04) ...
Selecting previously unselected package mysql-community-server.
Preparing to unpack mysql-community-server_5.7.18-1ubuntu16.04_amd64.deb ...
Unpacking mysql-community-server (5.7.18-1ubuntu16.04) ...
Setting up mysql-common (5.7.18-1ubuntu16.04) ...
update-alternatives: using /etc/mysql/my.cnf.fallback to provide /etc/mysql/my.cnf (my.cnf) in auto mode
dpkg: dependency problems prevent configuration of mysql-community-client:
 mysql-community-client depends on libaio1 (>= 0.3.93); however:
  Package libaio1 is not installed.

dpkg: error processing package mysql-community-client (--install):
 dependency problems - leaving unconfigured
Setting up libmysqlclient20:amd64 (5.7.18-1ubuntu16.04) ...
Setting up libmysqlclient-dev (5.7.18-1ubuntu16.04) ...
Setting up libmysqld-dev (5.7.18-1ubuntu16.04) ...
dpkg: dependency problems prevent configuration of mysql-client:
 mysql-client depends on mysql-community-client (= 5.7.18-1ubuntu16.04); however:
  Package mysql-community-client is not configured yet.

dpkg: error processing package mysql-client (--install):
 dependency problems - leaving unconfigured
dpkg: dependency problems prevent configuration of mysql-community-server:
 mysql-community-server depends on mysql-client (= 5.7.18-1ubuntu16.04); however:
  Package mysql-client is not configured yet.
 mysql-community-server depends on libaio1 (>= 0.3.93); however:
  Package libaio1 is not installed.
 mysql-community-server depends on libmecab2 (>= 0.996-1.2ubuntu1); however:
  Package libmecab2 is not installed.

dpkg: error processing package mysql-community-server (--install):
 dependency problems - leaving unconfigured
Processing triggers for man-db (2.7.5-1) ...
Processing triggers for libc-bin (2.23-0ubuntu7) ...
Processing triggers for systemd (229-4ubuntu10) ...
Processing triggers for ureadahead (0.100.0-19) ...
Errors were encountered while processing:
 mysql-community-client
 mysql-client
 mysql-community-server
  

5.执行依赖安装，依然报错如下。

root@ubuntu:~# apt-get install libaio1 libmecab2
Reading package lists... Done
Building dependency tree       
Reading state information... Done
You might want to run 'apt-get -f install' to correct these:
The following packages have unmet dependencies:
 openssh-server : Depends: openssh-client (= 1:7.2p2-4ubuntu2.2)
 openssh-sftp-server : Depends: openssh-client (= 1:7.2p2-4ubuntu2.2)
E: Unmet dependencies. Try 'apt-get -f install' with no packages (or specify a solution).

6.执行命令安依赖包。

 root@ubuntu:~# apt-get install libaio1 libmecab2 openssh-client
Reading package lists... Done
Building dependency tree       
Reading state information... Done
Suggested packages:
  ssh-askpass libpam-ssh keychain monkeysphere
The following NEW packages will be installed:
  libaio1 libmecab2
The following packages will be upgraded:
  openssh-client
1 upgraded, 2 newly installed, 0 to remove and 316 not upgraded.
3 not fully installed or removed.
Need to get 271 kB/857 kB of archives.
After this operation, 1,958 kB of additional disk space will be used.
Get:1 http://us.archive.ubuntu.com/ubuntu xenial/main amd64 libaio1 amd64 0.3.110-2 [6,356 B]
Get:2 http://us.archive.ubuntu.com/ubuntu xenial/universe amd64 libmecab2 amd64 0.996-1.2ubuntu1 [264 kB]
Fetched 271 kB in 3s (79.4 kB/s)    
Selecting previously unselected package libaio1:amd64.
(Reading database ... 208013 files and directories currently installed.)
Preparing to unpack .../libaio1_0.3.110-2_amd64.deb ...
Unpacking libaio1:amd64 (0.3.110-2) ...
Selecting previously unselected package libmecab2.
Preparing to unpack .../libmecab2_0.996-1.2ubuntu1_amd64.deb ...
Unpacking libmecab2 (0.996-1.2ubuntu1) ...
Preparing to unpack .../openssh-client_1%3a7.2p2-4ubuntu2.2_amd64.deb ...
Unpacking openssh-client (1:7.2p2-4ubuntu2.2) over (1:7.2p2-4ubuntu2.1) ...
Processing triggers for libc-bin (2.23-0ubuntu7) ...
Processing triggers for man-db (2.7.5-1) ...
Setting up libaio1:amd64 (0.3.110-2) ...
Setting up mysql-community-client (5.7.18-1ubuntu16.04) ...
Setting up mysql-client (5.7.18-1ubuntu16.04) ...
Setting up libmecab2 (0.996-1.2ubuntu1) ...
Setting up mysql-community-server (5.7.18-1ubuntu16.04) ...
Package configuration

7.要求输入root用户密码，输入以后mysql安装成功。

update-alternatives: using /etc/mysql/mysql.cnf to provide /etc/mysql/my.cnf (my.cnf) in auto mode                                                                 
Setting up openssh-client (1:7.2p2-4ubuntu2.2) ...
Processing triggers for libc-bin (2.23-0ubuntu7) ...
Processing triggers for systemd (229-4ubuntu10) ...
Processing triggers for ureadahead (0.100.0-19) ...

8.执行mysql免密码登陆报错。

root@ubuntu:~# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

9.进入/etc/mysql目录，编辑mysqld.cnf文件。在文件最后一行加入配置“skip-grant-tables”，执行命令“service mysql restart”重启mysql。

root@ubuntu:/etc/mysql/mysql.conf.d# vim mysqld.cnf
# Copyright (c) 2014, 2016, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA

#
# The MySQL  Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-error       = /var/log/mysql/error.log
# By default we only accept connections from localhost
bind-address    = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
skip-grant-tables

10.执行mysql命令，可以正确登陆mysql。

root@ubuntu:/etc/mysql/mysql.conf.d# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.18 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

11.安装完成，登陆以后可以执行命令修改mysql密码。

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> update mysql.user set authentication_string=password('root') where user='root';
Query OK, 1 row affected, 1 warning (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 1
mysql> flush privileges; 
Query OK, 0 rows affected (0.08 sec)
mysql> exit

12.将/etc/mysql/mysql.conf.d/mysqld.cnf最后一行删除，并执行命令“service mysql restart”重启mysql。

# Copyright (c) 2014, 2016, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA

#
# The MySQL  Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-error       = /var/log/mysql/error.log
# By default we only accept connections from localhost
bind-address    = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

13.输入命令“mysql -u root -p”，输入密码“root”，登陆成功。

root@ubuntu:/etc/mysql/mysql.conf.d# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.18 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

14.至此，MySQL Community Server 5.7.18 deb安装成功。

15.在两台服务器上分别安装MySQL Community Server 5.7.18 deb版本，修改主库配置文件
/etc/mysql/mysql.conf.d/mysqld.cnf,增加最后两行配置。

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-error       = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address   = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
server-id=107
log-bin=/var/lib/mysql/mysql-bin

16.从库也需要修改配置文件/etc/mysql/mysql.conf.d/mysqld.cnf，同样增加最后两行，注意server-id要不同。

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-error       = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address   = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-bin=/var/lib/mysql/mysql-bin
server-id=109

17.重启两台服务器mysql服务，service mysql restart。

18.主库建立账户并授予slave权限

mysql> GRANT REPLICATION SLAVE ON *.* to 'test'@'%' identified by 'test';
Query OK, 0 rows affected, 1 warning (0.01 sec)

19.查询主库状态

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |     1343 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

20.配置从库，停止从库复制功能；配置与主库同步信息，master_host为主库ip地址，master_user，master_password为18步创建的用户名和密码，master_log_file和master_log_pos为19步查询出的File和Position。 配置完成后，启动从库复制功能。

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> change master to
    -> master_host='192.168.244.128',
    -> master_user='test',
    -> master_password='test',
    -> master_log_file='mysql-bin.000004',
    -> master_log_pos=1343;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

21.检查下从库的配置信息，注意Slave_IO_Running: Yes和Slave_SQL_Running: Yes两个配置项必须为Yes，否则存在问题。需要重新执行19,20步进行配置。

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.244.128
                  Master_User: test
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 1343
               Relay_Log_File: ubuntu-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1343
              Relay_Log_Space: 528
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 107
                  Master_UUID: 2073267a-44e9-11e7-a5ce-000c29c1f067
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

22.配置完成，进行下测试。在主库创建database和table。

mysql> create database db_test;
Query OK, 1 row affected (0.01 sec)

mysql> use db_test;
Database changed
mysql> create table db_table(name char(13),age int(3));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into db_table values('tom', 19);
Query OK, 1 row affected (0.01 sec)

23.查询下主库信息。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db_test            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
8 rows in set (0.00 sec)

mysql> select * from db_table;
+------+------+
| name | age  |
+------+------+
| tom  |   19 |
+------+------+
1 row in set (0.00 sec)

24.查询下从库信息。发现从库中已经从主库中同步了db_test database以及db_table表数据。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db_test            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
7 rows in set (0.00 sec)

mysql> use db_test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from db_table;
+------+------+
| name | age  |
+------+------+
| tom  |   19 |
+------+------+
1 row in set (0.00 sec)

25.至此，Mysql完成了主从数据库同步配置和数据同步。

26.操作过程中遇到一个问题。在配置从库以后，出现Slave_IO_RunningConnecting和Slave_SQL_Running: Yes状态，查看下面错误信息，发现报错无法连接到主库ip，手动连接了一下报错“ERROR 2003 (HY000): Can't connect to MySQL server on 'xxx.xxx.xxx.xxx'(111)错误”，无法连接到主库。
解决过程：将主库/etc/mysql/mysql.conf.d/mysqld.cnf配置文件中bind-address= 127.0.0.1注销掉，重启主库即可，不要绑定本地回环地址。

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-error       = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address   = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
server-id=107
log-bin=/var/lib/mysql/mysql-bin